<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<html>
<head>
<!-- Copyright 1997 The Open Group, All Rights Reserved -->
<title>join</title>
</head><body bgcolor=white>
<center>
<font size=2>
The Single UNIX &reg; Specification, Version 2<br>
Copyright &copy; 1997 The Open Group

</font></center><hr size=2 noshade>
<h4><a name = "tag_001_014_1042">&nbsp;</a>NAME</h4><blockquote>
join - relational database operator
</blockquote><h4><a name = "tag_001_014_1043">&nbsp;</a>SYNOPSIS</h4><blockquote>
<pre><code>

join <b>[ </b>-a <i>file_number</i> | -v <i>file_number </i><b>][</b>-e <i>string</i><b>][</b>-o <i>list</i><b>][</b>-t <i>char</i><b>]
[</b>-1 <i>field</i><b>][</b>-2 <i>field</i><b>]</b><i> file1 file2</i>

join <b>[</b>-a <i>file_number</i><b>][</b>-e <i>string</i><b>][</b>-j <i>field</i><b>][</b>-j1 <i>field</i><b>][</b>-j2 <i>field</i><b>]
[</b>-o <i>list</i>...<b>][</b>-t <i>char</i><b>][</b>-t <i>char</i><b>] </b><i>file1 file2</i>
</code>
</pre>
</blockquote><h4><a name = "tag_001_014_1044">&nbsp;</a>DESCRIPTION</h4><blockquote>
The
<i>join</i>
utility will perform an equality join on the files
<i>file1</i>
and
<i>file2</i>.
The joined files will be written to the standard output.
<p>
The join field is a field in each file on which the files
are compared.
By default,
<i>join</i>
writes one line in the output
for each pair of lines in
<i>file1</i>
and
<i>file2</i>
that have identical join fields.
The output line by default will consist of the join field,
then the remaining fields from
<i>file1</i>,
then the remaining fields from
<i>file2</i>.
This format can be changed by using the
<b>-o</b>
option (see below).
The
<b>-a</b>
option can be used to add unmatched lines to the output.
The
<b>-v</b>
option can be used to output only unmatched lines.
<p>
By default, the files
<i>file1</i>
and
<i>file2</i>
should be ordered in the collating sequence of
<i><a href="sort.html">sort</a></i>
<b>-b</b>
on the fields
on which they are to be joined,
by default the first in each line.
All selected output will be written in the same collating sequence.
<p>
The default input field separators will be
blank characters.
In this case, multiple separators will count as one field separator, and
leading separators will be ignored.
The default output field separator will be a
space character.
<p>
The field separator and collating sequence can be changed by using the
<b>-t</b>
option (see below).
<p>
If the input files are not in the appropriate collating sequence,
the results are unspecified.
</blockquote><h4><a name = "tag_001_014_1045">&nbsp;</a>OPTIONS</h4><blockquote>
The
<i>join</i>
utility supports the <b>XBD</b> specification, <a href="../xbd/utilconv.html#usg"><b>Utility Syntax Guidelines</b>&nbsp;</a> .
The obsolescent version does not follow the utility argument
syntax guidelines:
the
<b>-j1</b>
and
<b>-j2</b>
options are multi-character options and the
<b>-o</b>
option takes multiple arguments.
<p>
The following options are supported:
<dl compact>

<dt><b>-a&nbsp;</b><i>file_number</i>
<dd>
Produce a line for each unpairable line in file
<i>file_number</i>,
where
<i>file_number</i>
is
1
or
2,
in addition to the default output.
If both
<b>-a 1</b>
and
<b>-a 2</b>
are specified, all unpairable lines will be output.

<dt><b>-e&nbsp;</b><i>string</i>
<dd>
Replace empty output fields in the list selected by
<b>-o</b>
with the string
<i>string</i>.

<dt><b>-j </b><i>field</i><dd>Equivalent to:
<b>-1</b>
<i>field</i>
<b>-2</b>
<i>field</i>.

<dt><b>-j1 </b><i>field</i><dd>Equivalent to:
<b>-1</b>
<i>field</i>.

<dt><b>-j2 </b><i>field</i><dd>Equivalent to:
<b>-2</b>
<i>field</i>.

<dt><b>-o&nbsp;</b><i>list</i>
<dd>Construct the output line to comprise the fields specified in
<i>list</i>,
each element of which
has one of the following two forms:
<ul>

<li>
<i>file_number.field</i>,
where
<i>file_number</i>
is a file number and
<i>field</i>
is a decimal integer field number

<li>
0
(zero), representing the join field.

</ul>

The elements of
<i>list</i>
are either comma- or
blank-separated,
as specified in Guideline 8 of the <b>XBD</b> specification, <a href="../xbd/utilconv.html#usg"><b>Utility Syntax Guidelines</b>&nbsp;</a> .
The fields specified by
<i>list</i>
will be written for all selected output lines.
Fields selected by
<i>list</i>
that do not appear in the
input will be treated as empty output fields.
(See the
<b>-e</b>
option.)
Only specifically requested fields are written.
The
<i>list</i>
must be a single command line argument.
However, as an obsolescent feature,
the argument
<i>list</i>
can be multiple arguments on the command line.

<dt><b>-t&nbsp;</b><i>char</i>
<dd>Use character
<i>char</i>
as a separator, for both input and output.
Every appearance of
<i>char</i>
in a line will be significant.
When this option is specified, the collating sequence
should be the same as
<i><a href="sort.html">sort</a></i>
without the
<b>-b</b>
option.

<dt><b>-v&nbsp;</b><i>file_number</i>
<dd>
Instead of the default output, produce a line only for each
unpairable line in <i>file_number</i>, where <i>file_number</i> is 1 or 2.
If both
<b>-v 1</b>
and
<b>-v 2</b>
are specified, all unpairable lines will be output.

<dt><b>-1&nbsp;</b><i>field</i>
<dd>Join on the
<i>field</i>th
field of file 1.
Fields are decimal integers starting with 1.

<dt><b>-2&nbsp;</b><i>field</i>
<dd>Join on the
<i>field</i>th
field of file 2.
Fields are decimal integers starting with 1.

</dl>
</blockquote><h4><a name = "tag_001_014_1046">&nbsp;</a>OPERANDS</h4><blockquote>
The following operands are supported:
<dl compact>

<dt><i>file1</i><dd>
<dt><i>file2</i><dd>A pathname of a file to be joined.
If either of the
<i>file1</i>
or
<i>file2</i>
operands is "-", the standard input is used in its place.

</dl>
</blockquote><h4><a name = "tag_001_014_1047">&nbsp;</a>STDIN</h4><blockquote>
The standard input will be used only if the
<i>file1</i>
or
<i>file2</i>
operand is "-".
See the INPUT FILES section.
</blockquote><h4><a name = "tag_001_014_1048">&nbsp;</a>INPUT FILES</h4><blockquote>
The input files must be text files.
</blockquote><h4><a name = "tag_001_014_1049">&nbsp;</a>ENVIRONMENT VARIABLES</h4><blockquote>
The following environment variables affect the execution of
<i>join</i>:
<dl compact>

<dt><i>LANG</i><dd>Provide a default value for the internationalisation variables
that are unset or null.
If
<i>LANG</i>
is unset or null, the corresponding value from the
implementation-dependent default locale will be used.
If any of the internationalisation variables contains an invalid setting, the
utility will behave as if none of the variables had been defined.

<dt><i>LC_ALL</i><dd>
If set to a non-empty string value,
override the values of all the other internationalisation variables.

<dt><i>LC_COLLATE</i><dd>
Determine the locale of the
collating sequence
<i>join</i>
expects to have been used when the
input files were sorted.

<dt><i>LC_CTYPE</i><dd>
Determine the
locale for the interpretation of sequences of bytes of text data as
characters (for example, single- as opposed to multi-byte characters
in arguments and input files).

<dt><i>LC_MESSAGES</i><dd>
Determine the locale that should be used to affect
the format and contents of diagnostic
messages written to standard error.

<dt><i>NLSPATH</i><dd>
Determine the location of message catalogues
for the processing of
<i>LC_MESSAGES .
</i>
</dl>
</blockquote><h4><a name = "tag_001_014_1050">&nbsp;</a>ASYNCHRONOUS EVENTS</h4><blockquote>
Default.
</blockquote><h4><a name = "tag_001_014_1051">&nbsp;</a>STDOUT</h4><blockquote>
The
<i>join</i>
utility output will be a concatenation of selected character fields.
When the
<b>-o</b>
option is not specified, the output will be:
<p><code>
<pre>
<tt>"%s%s%s\n"</tt>, &lt;<i>join field</i>&gt;,
&lt;<i>other file1 fields</i>&gt;,
&lt;<i>other file2 fields</i>&gt;
</pre>
</code>
<p>
If the join field is not the first field in
a file, the
&lt;<i>other&nbsp;file&nbsp;fields</i>&gt;
for that file are:
<p><code>
<pre>
&lt;<i>fields preceding join field</i>&gt;,
&lt;<i>fields following join field</i>&gt;
</pre>
</code>
<p>
When the
<b>-o</b>
option is specified, the output format will be:
<p><code>
<tt>"%s\n"</tt>, &lt;<i>concatenation of fields</i>&gt;
</code>
where the concatenation of fields is described by the
<b>-o</b>
option, above.
<p>
For either format,
each field (except the last)
will be written with its trailing separator character.
If the separator is the default
(blank characters),
a single
space
character will be written after each field (except the last).
</blockquote><h4><a name = "tag_001_014_1052">&nbsp;</a>STDERR</h4><blockquote>
Used only for diagnostic messages.
</blockquote><h4><a name = "tag_001_014_1053">&nbsp;</a>OUTPUT FILES</h4><blockquote>
None.
</blockquote><h4><a name = "tag_001_014_1054">&nbsp;</a>EXTENDED DESCRIPTION</h4><blockquote>
None.
</blockquote><h4><a name = "tag_001_014_1055">&nbsp;</a>EXIT STATUS</h4><blockquote>
The following exit values are returned:
<dl compact>

<dt>0<dd>All input files were output successfully.

<dt>&gt;0<dd>An error occurred.

</dl>
</blockquote><h4><a name = "tag_001_014_1056">&nbsp;</a>CONSEQUENCES OF ERRORS</h4><blockquote>
Default.
</blockquote><h4><a name = "tag_001_014_1057">&nbsp;</a>APPLICATION USAGE</h4><blockquote>
Pathnames consisting of numeric digits or of the form
<i>string.string</i>
should not be specified
directly following the
<b>-o</b>
list.
</blockquote><h4><a name = "tag_001_014_1058">&nbsp;</a>EXAMPLES</h4><blockquote>
The
<b>-o&nbsp;0</b>
field essentially selects the union of the join fields.
For example, given file
<b>phone</b>:
<pre>
<code>
!Name           Phone Number
Don             +1 123-456-7890
Hal             +1 234-567-8901
Yasushi         +2 345-678-9012
</code>
</pre>
and file
<b>fax</b>:
<pre>
<code>
!Name           Fax Number
Don             +1 123-456-7899
Keith           +1 456-789-0122
Yasushi         +2 345-678-9011
</code>
</pre>
(where the large expanses of white space
are meant to each represent a single
tab character),
the command:
<pre>
<code>
join -t "&lt;tab&gt;" -a 1 -a 2 -e '(unknown)' -o 0,1.2,2.2 phone fax
</code>
</pre>
would produce:
<pre>
<code>
!Name           Phone Number            Fax Number
Don             +1 123-456-7890         +1 123-456-7899
Hal             +1 234-567-8901         (unknown)
Keith           (unknown)               +1 456-789-0122
Yasushi         +2 345-678-9012         +2 345-678-9011
</code>
</pre>
</blockquote><h4><a name = "tag_001_014_1059">&nbsp;</a>FUTURE DIRECTIONS</h4><blockquote>
The obsolescent
<b>-j</b>
options and the multi-argument
<b>-o</b>
option may be withdrawn in a future issue.
</blockquote><h4><a name = "tag_001_014_1060">&nbsp;</a>SEE ALSO</h4><blockquote>
<i><a href="awk.html">awk</a></i>,
<i><a href="comm.html">comm</a></i>,
<i><a href="sort.html">sort</a></i>,
<i><a href="uniq.html">uniq</a></i>.
</blockquote><hr size=2 noshade>
<center><font size=2>
UNIX &reg; is a registered Trademark of The Open Group.<br>
Copyright &copy; 1997 The Open Group
<br> [ <a href="../index.html">Main Index</a> | <a href="../xshix.html">XSH</a> | <a href="../xcuix.html">XCU</a> | <a href="../xbdix.html">XBD</a> | <a href="../cursesix.html">XCURSES</a> | <a href="../xnsix.html">XNS</a> ]

</font></center><hr size=2 noshade>
</body></html>
